package Database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;
import java.util.Vector;

public class DBActions {

private Vector<UserInfo> recordTable;
private Vector<PlayerPlayedGames> allPlayedGames;
public Connection Connect() throws ClassNotFoundException, SQLException{
	if (!ConnectionPool.Connect())
		return null;
	Connection conn=ConnectionPool.getConnection();
	ConnectionPool.returnConnection(conn);
	return conn;
}

public UserInfo AuthenticateUser(String username, String password) throws SQLException, ClassNotFoundException, DBException{
	if (!ConnectionPool.Connect())
		return null;
	Connection conn=ConnectionPool.getConnection();
	PreparedStatement ps;
	
	ps = conn.prepareStatement("SELECT * FROM USERS WHERE username=? AND password=?");
	ps.setString(1, username);
	ps.setString(2, password);
	ps.execute();
	ResultSet rs = ps.getResultSet();
	ConnectionPool.returnConnection(conn);
	if (!rs.next())
		throw new DBException("Incorrect username or password.");
	Connection conn1=ConnectionPool.getConnection();
	ps = conn1.prepareStatement("UPDATE users SET LastLogin = ? WHERE Username= ?");
	DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
    Date date = new Date();
	ps.setString(1, dateFormat.format(date));
	ps.setString(2, username);
	ps.execute();
	ConnectionPool.returnConnection(conn1);
	return getUserInfo(username);
		
}

public void AddUser(String username, String password, String email) throws DBException, SQLException, ClassNotFoundException{
		if (!ConnectionPool.Connect())
			return;
		Connection conn=ConnectionPool.getConnection();
		UserInfo user;
		try {
			user = getUserInfo(username);
		} catch (ClassNotFoundException e1) {
			throw e1;
		}
		if (user!=null)
			throw new DBException("Username already exists");
		try{
			PreparedStatement ps=conn.prepareStatement("INSERT INTO USERS(username, password, email) VALUES(?, ?, ?)");
			ps.setString(1, username);
			ps.setString(2, password);
			ps.setString(3, email);
			ps.execute();
		}catch(Exception e){
			throw new DBException("Error in sql statement.");
		}
		ConnectionPool.returnConnection(conn);
}
	
/**
 * @return the recordTable
 * @throws SQLException
 * @throws ClassNotFoundException
 * @throws DBException 
 */
private Vector<UserInfo> getRecordTable() throws SQLException,
ClassNotFoundException, DBException {
	String sql;
	if (this.recordTable==null)
		this.recordTable = new Vector<UserInfo>();
	else
		this.recordTable.clear();
	if (!ConnectionPool.Connect())
		return null;
	Connection conn = ConnectionPool.getConnection();
	sql="SELECT TOP 10 rating, username, amountgames FROM users ORDER BY rating";
	Statement s = conn.createStatement();
	ResultSet rs = s.executeQuery(sql);
	if (!rs.next())
		return recordTable;
	else
		do {
			UserInfo uInfo = getUserInfo(rs.getString("username"));
			if (uInfo == null)
				throw new DBException("Username " + rs.getString("username") + " not found.");
			recordTable.addElement(uInfo);
		} while (rs.next());
	ConnectionPool.returnConnection(conn);
	return recordTable;
}
public String getRecordTableString() throws SQLException, ClassNotFoundException, DBException{
	Vector<UserInfo> table = getRecordTable();
	String str="Score\tUsername\tGames\n";
	 // keySet method returns a Set view of the keys contained in this map.
    Iterator<UserInfo> iterator = table.iterator();
    while (iterator.hasNext()) {
      UserInfo user = iterator.next();
      str += user.getScore() + "\t";
      str += user.getUsername() + "\t";
      str += user.getAmountGames() + "\n";
    }
	return str;
}
public static UserInfo getUserInfo(String username) throws ClassNotFoundException, SQLException {
	if (!ConnectionPool.Connect())
		return null;
	Connection conn = ConnectionPool.getConnection();
	PreparedStatement ps = conn.prepareStatement("SELECT * FROM users WHERE username = ?");
	ps.setString(1, username);
	ps.execute();
	ResultSet rs = ps.getResultSet();
	ConnectionPool.returnConnection(conn);
	if (!rs.next())
		return null;
	else{
		String susername= rs.getString("Username");
		UserInfo uInfo = new UserInfo(susername);
		uInfo.setEmail(rs.getString("Email"));
		uInfo.setAmountGames(rs.getInt("amountGames"));
		uInfo.setCreationDate(rs.getDate("creationDate"));
		uInfo.setLastLogin(rs.getDate("lastLogin"));
		uInfo.setScore(rs.getInt("Rating"));
		uInfo.setId(rs.getLong("userID"));
		return uInfo;
	}
}
private Vector<PlayerPlayedGames> getUserPlayedGames(String username) throws ClassNotFoundException, SQLException {
	allPlayedGames = new Vector<PlayerPlayedGames>();
	if (!ConnectionPool.Connect())
		return null;
	Connection conn = ConnectionPool.getConnection();
	PreparedStatement ps = conn.prepareStatement("SELECT GameID FROM qryGameHistory WHERE username = ?");
	ps.setString(1, username);
	ps.execute();
	ResultSet rs = ps.getResultSet();
	//ConnectionPool.returnConnection(conn);
	while (rs.next()){
		long id=rs.getLong("GameID");
		Connection conn1 = ConnectionPool.getConnection();
		Statement s = conn1.createStatement();
		s.execute("SELECT * FROM qryGameHistory WHERE GameID =" + id);
	//	PreparedStatement ps1 = conn1.prepareStatement("SELECT * FROM qryGameHistory WHERE GameID = ?");
		ResultSet rs1 = s.getResultSet();
		ConnectionPool.returnConnection(conn1);
		while (rs1.next()){
			PlayerPlayedGames pg = new PlayerPlayedGames(id, rs1.getDate("GameDate"), rs1.getString("Username"),rs1.getInt("Rating"),rs1.getBoolean("Winner"));
			allPlayedGames.add(pg);
		}
	}
	return allPlayedGames;
}
public String getUserPlayedGamesString(String username) throws ClassNotFoundException, SQLException{
	Vector<PlayerPlayedGames> table = getUserPlayedGames(username);
    Iterator<PlayerPlayedGames> iterator = table.iterator();
    String str="";
    Long oldGameID=null;
    while (iterator.hasNext()) {
    	PlayerPlayedGames pg = iterator.next();
    	if (oldGameID==null){
    		oldGameID=pg.getGameID();
    		str += pg.getGameDate().toString();
    	}else if (!oldGameID.equals(pg.getGameID())){
    		str += "\n" + pg.getGameDate().toString();
    	}
    	str += "\n\t";
    	str += pg.getUsername();
    	if (pg.isWinner()){
    		str += " is the winner with rating ";
    		str += pg.getRating() + "\t";
    	}
    	else
    		str += "'s rating is " + pg.getRating();
    	oldGameID=pg.getGameID();
    }
	return str;
}

public static boolean AddGame(Vector<PlayerPlayedGames> vec) throws SQLException, ClassNotFoundException{
	Long gameID = null;
	if (!ConnectionPool.Connect())
		return false;
	if (!vec.isEmpty()){
		Connection conn=ConnectionPool.getConnection();
		PreparedStatement ps = conn.prepareStatement("INSERT INTO games(GameDate) VALUES (?)");
		DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
		ps.setString(1, dateFormat.format(vec.get(0).getGameDate()));
		ps.execute();
		ps=conn.prepareStatement("SELECT GameID FROM Games WHERE GameDate = ?");
		ps.setString(1, dateFormat.format(vec.get(0).getGameDate()));
		ps.execute();
		ResultSet rs=ps.getResultSet();
		if (rs.next())
			gameID = rs.getLong("GameID");
		ConnectionPool.returnConnection(conn);
	}
	if (gameID!=null){
		Connection conn=ConnectionPool.getConnection();
		Statement s = conn.createStatement();
		for (PlayerPlayedGames pg : vec){
			String str="INSERT INTO gameDetails(GameID, UserID, Rating, Winner) VALUES (" + gameID;
			str += ", " + pg.getUserID() + ", " + pg.getRating() + ", " + pg.isWinner() + ")";

			if (pg.isWinner())
			//if (false)
				s.addBatch("UPDATE users SET Rating=Rating+1, AmountGames=AmountGames+1 WHERE UserID=" + pg.getUserID());
			else
				s.addBatch("UPDATE users SET AmountGames=AmountGames+1 WHERE UserID=" + pg.getUserID());
			System.out.println("Done\n");
			try {
				Thread.sleep(1000);
			} catch (InterruptedException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			s.addBatch(str);
		}
		s.addBatch("update users set userName=userName where userID=-1"); // dummy 
		s.executeBatch();
		conn.commit();
		
		ConnectionPool.returnConnection(conn);

		return true;
	}
	else
		return false;
}
public String getUserProperties(String username) throws ClassNotFoundException, SQLException{
	UserInfo user= getUserInfo(username);
	String str="";
	if (user != null){
		str += "Username: " + user.getUsername() + "\n";
		str += "Email: " + user.getEmail() + "\n";
		str += "Wins: " + user.getScore() + "\n";
		str += "Number of games: " + user.getAmountGames() + "\n";
		DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
		str += "Reg Date: " + dateFormat.format(user.getCreationDate()) + "\n";
		String lastLogin = user.getLastLogin()==null ? "none" : dateFormat.format(user.getLastLogin()); 
		str += "Last Login: " + lastLogin + "\n";
	}
	return str;
}
}
